Course: MySQL Training
Topic: Revoking Permissions Using REVOKE
Course Link: MySQL Training Course – JustAcademy
Demo Link: Register for a Free Course Demo
MySQL provides a privilege management system that allows database administrators to control which users can access databases, tables, columns, and other database objects. Permissions determine whether a user can perform operations such as reading data, inserting records, updating information, deleting rows, creating tables, or modifying database structures.
Sometimes, a user no longer needs a particular permission. For example, an employee may move to another department, a developer may finish a project, or an external contractor may no longer require access to a database. In these situations, the administrator should remove unnecessary permissions to protect the database.
MySQL provides the REVOKE statement to remove previously granted privileges or roles from users and roles. It helps administrators maintain controlled access and follow the principle of least privilege.
Suppose a database administrator gives a reporting employee permission to read records from the company_db database.
GRANT SELECT ON company_db.* TO 'report_user'@'localhost';If the employee no longer requires read access, the administrator can remove that permission using:
REVOKE SELECT ON company_db.* FROM 'report_user'@'localhost';After the privilege is revoked, the user no longer has that particular database-level SELECT grant. However, access might still be available through another grant or an active role, so the administrator should inspect the user's remaining privileges.
After studying this topic, you will be able to:
REVOKE statement.GRANT and REVOKE.REVOKE statement.SHOW GRANTS.The REVOKE statement is a MySQL Data Control Language (DCL) statement used to remove specific privileges or roles previously assigned to a user account or role.
It allows administrators to reduce a user's access without necessarily deleting the user account.
REVOKE privilege_name
ON database_name.table_name
FROM 'username'@'host';| Component | Description | Example |
|---|---|---|
REVOKE |
Keyword used to remove permissions. | REVOKE |
privilege_name |
The permission being removed. | SELECT |
ON |
Specifies the database object or privilege level. | ON company_db.* |
FROM |
Identifies the account or role losing the privilege. | FROM 'report_user'@'localhost' |
username |
The MySQL account name. | report_user |
host |
The host part of the account identity. | localhost |
Important: MySQL identifies an account using both its username and host. Therefore, 'report_user'@'localhost' and 'report_user'@'%' may be different accounts with different permissions.
The GRANT statement assigns permissions, while REVOKE removes specified permissions. These statements are commonly used together to manage database security.
| Feature | GRANT | REVOKE |
|---|---|---|
| Purpose | Provides privileges. | Removes privileges. |
| Direction | Assigns access to a user or role. | Withdraws specified access from a user or role. |
| Keyword | TO |
FROM |
| Example | GRANT SELECT ON company_db.* TO 'user1'@'localhost'; |
REVOKE SELECT ON company_db.* FROM 'user1'@'localhost'; |
| Account deletion | Does not create an account by itself in the usual privilege-assignment workflow. | Does not delete the user account. |
| Common use | Give a developer permission to update records. | Remove update access when it is no longer needed. |
MySQL supports several privileges that control different database operations. The privilege level determines the scope of access that is being granted or revoked.
| Privilege | Purpose |
|---|---|
SELECT |
Read data from tables or views. |
INSERT |
Add new records. |
UPDATE |
Modify existing records. |
DELETE |
Delete records. |
CREATE |
Create databases or tables at supported privilege levels. |
ALTER |
Modify table structures. |
DROP |
Drop supported database objects. |
INDEX |
Create or remove indexes. |
EXECUTE |
Execute stored routines at applicable privilege levels. |
TRIGGER |
Create or drop triggers on applicable tables. |
GRANT OPTION |
Allows an account to grant privileges it is authorized to grant. |
ALL PRIVILEGES |
Represents the privileges available at the specified level, subject to MySQL's rules and exclusions. |
| Level | Example | Scope |
|---|---|---|
| Global | *.* |
Across the server's databases, subject to the privilege type. |
| Database | company_db.* |
All applicable objects in a specific database. |
| Table | company_db.employees |
A specific table. |
| Column | company_db.employees (salary) |
A particular column for supported column-level privileges. |
| Routine | PROCEDURE company_db.update_salary |
A particular stored procedure or function, depending on the privilege. |
Database-level privileges apply to the applicable objects within a particular database. If a user no longer needs access to every table in a database, the administrator can revoke a database-level privilege.
Assume that report_user has been granted permission to read data from the company_db database.
GRANT SELECT ON company_db.* TO 'report_user'@'localhost';To remove the read permission, execute:
REVOKE SELECT ON company_db.* FROM 'report_user'@'localhost';This statement removes the specified database-level SELECT grant. It does not automatically remove other privileges that the account may have received.
Table-level privileges apply to a specific table rather than all tables in a database. This is useful when users need access to some tables but not others.
Suppose the user has permission to read the employees table.
GRANT SELECT ON company_db.employees
TO 'analyst_user'@'localhost';To remove that permission:
REVOKE SELECT ON company_db.employees
FROM 'analyst_user'@'localhost';The user loses this specific table-level grant. However, if the user also has SELECT permission through a database-level grant, global grant, or active role, the user may still be able to read the table.
REVOKE DELETE ON company_db.employees
FROM 'analyst_user'@'localhost';This removes the applicable table-level DELETE grant. It does not remove SELECT, INSERT, or UPDATE grants unless those privileges are explicitly included in the statement.
MySQL allows administrators to revoke multiple privileges in a single statement. This is useful when a user should retain read access but lose the ability to modify data.
REVOKE privilege1, privilege2, privilege3
ON database_name.table_name
FROM 'username'@'host';Suppose an application user has permissions to insert, update, and delete records from the orders table. The administrator wants to remove all three modification permissions.
REVOKE INSERT, UPDATE, DELETE
ON company_db.orders
FROM 'app_user'@'localhost';The statement removes the specified privileges at the table level. Any remaining privileges, such as SELECT, are unaffected by this command.
Sometimes, an account must lose all its assigned privileges. MySQL supports statements for removing global, database, table, column, and routine privileges from a user or role.
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'temporary_user'@'localhost';This statement removes the applicable privileges and the ability to grant privileges to others. It does not delete the account, and it does not automatically revoke roles assigned to that account.
REVOKE ALL ON *.*
FROM 'temporary_user'@'localhost';At the global level, this removes granted global static privileges and granted dynamic privileges. It does not revoke roles, and it does not delete the user account.
Neither of these statements removes assigned roles. If the account receives permissions through roles, those roles must be reviewed separately. MySQL also requires suitable administrative privileges to execute these statements.
The GRANT OPTION privilege allows an account to grant privileges it is authorized to grant to other accounts or roles. It should be assigned carefully because it gives the account additional responsibility over access management.
Suppose a team leader has been allowed to grant database permissions to other users.
GRANT SELECT ON company_db.*
TO 'team_lead'@'localhost'
WITH GRANT OPTION;To remove the grant option and the privileges covered by this statement, use:
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'team_lead'@'localhost';Note: This command removes all applicable privileges, not only the grant option. If you need to retain the user's other permissions, review the existing grants and use a more targeted approach where appropriate. MySQL does not provide a simple standalone REVOKE GRANT OPTION form for an account; the documented form combines it with ALL PRIVILEGES.
Column-level privileges can limit access to selected columns in a table. This can be useful when a user needs access to certain information but should not be able to modify sensitive fields.
Assume a user has permission to update the department column in the employees table.
GRANT UPDATE (department)
ON company_db.employees
TO 'hr_user'@'localhost';To revoke that specific column-level privilege:
REVOKE UPDATE (department)
ON company_db.employees
FROM 'hr_user'@'localhost';The administrator should verify the remaining grants because a broader table-level or global UPDATE privilege may still allow the user to modify that column.
MySQL allows a privilege to be revoked from multiple accounts in one statement, provided the accounts and syntax are valid.
REVOKE SELECT ON company_db.*
FROM 'intern1'@'localhost',
'intern2'@'localhost';This attempts to revoke the database-level SELECT grant from both accounts. Without options such as IF EXISTS or IGNORE UNKNOWN USER, the statement can fail as a whole if an error occurs for a named account or grant.
A role is a named collection of privileges. Instead of assigning many individual privileges to every user, administrators can create roles and grant them to users. MySQL also allows roles to be revoked.
CREATE ROLE 'reporting_role';
GRANT SELECT ON company_db.* TO 'reporting_role';
GRANT 'reporting_role' TO 'analyst_user'@'localhost';If the analyst no longer requires the role, remove it using:
REVOKE 'reporting_role'
FROM 'analyst_user'@'localhost';This removes the role assignment from the account. It does not delete the role itself or the account. Roles configured as mandatory cannot be revoked from users through the ordinary role-revocation process.
An administrator can also remove privileges from a role. This affects accounts that receive those privileges through that role.
REVOKE INSERT, UPDATE, DELETE
ON company_db.*
FROM 'reporting_role';Use this approach carefully because modifying a shared role can affect multiple users at once.
MySQL privileges are generally additive: an account can receive access from global, database, table, column, and role-based grants. Removing a lower-level grant does not automatically cancel a privilege granted at a higher level.
GRANT SELECT, INSERT ON *.*
TO 'app_user'@'localhost';The account receives global SELECT and INSERT privileges. A normal database-level revoke does not necessarily cancel the global grant.
MySQL 8.0 and later support partial revokes through the partial_revokes system variable. When enabled, an administrator can restrict a global privilege from applying to a particular database.
SET PERSIST partial_revokes = ON;
REVOKE INSERT ON company_db.*
FROM 'app_user'@'localhost';This is a special case: the partial revoke feature must be enabled, and the statement creates a restriction on the global privilege for that database. If the feature is disabled, attempting this kind of partial revoke may fail when the user only has the privilege globally.
Before changing this server-wide setting, review the official documentation and test the change in a safe environment.
After revoking permissions, administrators should verify the account's remaining grants. The SHOW GRANTS statement displays the privileges and roles assigned to a user or role.
SHOW GRANTS FOR 'username'@'host';SHOW GRANTS FOR 'report_user'@'localhost';Suppose the account originally had SELECT and INSERT on company_db. After revoking INSERT, the output should no longer show that database-level INSERT grant. The account may still have other grants or role assignments.
SHOW GRANTS FOR 'username'@'host'; before making changes.REVOKE statement.SHOW GRANTS again to inspect the remaining grants.No. The REVOKE statement removes specified privileges or role assignments, but it does not delete the MySQL account.
If the account must be removed completely, use DROP USER after confirming that deletion is appropriate.
REVOKE SELECT ON company_db.*
FROM 'former_employee'@'localhost';
-- Remove the account only if it is no longer required.
DROP USER 'former_employee'@'localhost';Deleting a user is a separate administrative action. Review dependencies, scheduled tasks, application configurations, and audit requirements before dropping an account.
| Error or Problem | Possible Cause | Solution |
|---|---|---|
| Access denied | The administrator lacks the required privileges. | Use an authorized account with the necessary privilege-management permissions. |
| No such grant defined | The specified privilege was not granted at that exact level, or the account details are incorrect. | Run SHOW GRANTS and verify the user, host, privilege, and object. |
| User does not exist | The username or host does not match an existing account. | Verify the exact account identity. |
| User still accesses the table | Another global, database, table, column, or role-based grant provides access. | Inspect all grants and role assignments. |
| Role privileges remain available | The role has not been revoked, or another active role provides the same access. | Review role assignments, active roles, and the privileges granted to each role. |
| Partial revoke fails | The required partial-revocation feature is not enabled, or the privilege was not granted at the expected level. | Check partial_revokes, existing grants, and the relevant MySQL version documentation. |
| Syntax error | Incorrect use of ON, FROM, object names, or account quoting. |
Check the statement syntax and quote the account as 'username'@'host'. |
In supported MySQL versions, the IF EXISTS option allows certain revoke operations to issue a warning instead of an error when the target account or role exists but the specified privilege or role assignment is not found. It does not mean that a nonexistent user is silently accepted in every case.
REVOKE IF EXISTS SELECT
ON company_db.employees
FROM 'report_user'@'localhost';After execution, review any warnings if the expected grant was not present.
SHOW WARNINGS;Use this option only when its behavior is supported by the MySQL version in use. For routine permission management, verify the intended account and privileges before executing a revoke operation.
REVOKE when access must be reduced but the account is still needed.Scenario: An application account can read and modify records in the company_db database. The company wants to make the account read-only while retaining its database-level SELECT permission.
SHOW GRANTS FOR 'app_user'@'localhost';REVOKE INSERT, UPDATE, DELETE
ON company_db.*
FROM 'app_user'@'localhost';SHOW GRANTS FOR 'app_user'@'localhost';Connect as the application account in a controlled environment. Confirm that permitted read operations still work and that insert, update, and delete operations are denied if no other grant or active role provides those permissions.
Expected outcome: The account loses the specified database-level write privileges while retaining unrelated grants. The exact result depends on all grants and roles assigned to the account.
SELECT on company_db.* to 'student1'@'localhost', then revoke it.INSERT and UPDATE on company_db.employees, then revoke only UPDATE.INSERT, UPDATE, and DELETE from 'app_user'@'localhost' at the database level.SHOW GRANTS before and after a revoke operation and compare the results.data_editor, grant it table-level privileges, assign it to a test user, and then revoke the role assignment.SELECT grant has been revoked.Project objective: Audit a test database and remove unnecessary permissions while preserving required access.
company_db.SHOW GRANTS.CREATE DATABASE IF NOT EXISTS company_db;
CREATE USER IF NOT EXISTS 'report_user'@'localhost';
CREATE USER IF NOT EXISTS 'app_user'@'localhost';
GRANT SELECT ON company_db.*
TO 'report_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE
ON company_db.*
TO 'app_user'@'localhost';
SHOW GRANTS FOR 'report_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';
REVOKE INSERT, UPDATE, DELETE
ON company_db.*
FROM 'app_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';Run this workflow only in a practice environment with an authorized administrative account. Account creation, privilege assignment, and revocation require appropriate permissions.
Answer: REVOKE removes specified privileges or role assignments from a user account or role.
Answer: GRANT assigns privileges, whereas REVOKE removes specified privileges.
Answer: No. It removes privileges or role assignments. DROP USER is used to delete an account.
REVOKE SELECT ON company_db.*
FROM 'report_user'@'localhost';Answer: List the privileges separated by commas in the same REVOKE statement.
REVOKE INSERT, UPDATE, DELETE
ON company_db.*
FROM 'app_user'@'localhost';Answer: Use SHOW GRANTS FOR 'username'@'host';.
Answer: Revoking the table-level grant does not remove the global grant. The user may retain access through the broader grant.
Answer: Yes. A role assignment can be removed using REVOKE 'role_name' FROM 'username'@'host';.
Answer: It removes the applicable privileges and grant option from the named user or role. It does not automatically remove assigned roles or delete the account.
Answer: The user may have another grant at a broader level, an applicable column privilege, or access through an active role.
Answer: A partial revoke restricts a global privilege from applying to a particular database when MySQL's partial_revokes feature is enabled.
Answer: It helps verify which privileges and role assignments remain after the operation.
REVOKE removes specified privileges or roles.GRANT assigns privileges; REVOKE removes them.ON to specify the privilege level and database object.FROM to specify the account or role losing access.database_name.* for applicable database-level privileges.database_name.table_name for table-level privileges.SHOW GRANTS verifies the remaining grants.REVOKE does not delete the account.The MySQL REVOKE statement is an essential tool for managing database security. It allows administrators to remove unnecessary permissions from users and roles at the appropriate privilege level. By using targeted revokes, checking remaining access with SHOW GRANTS, reviewing role assignments, and understanding global privileges, administrators can reduce unauthorized access while keeping required database operations available.
For reliable permission management, always verify the exact username and host, inspect existing grants before making changes, and test the resulting access. Remember that revoking a specific grant does not necessarily remove equivalent access provided by another grant or role.
